package org.svnadmin.dao;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.svnadmin.constant.Constants;
import org.svnadmin.entity.Pj;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 项目DAO
 *
 * @author <a href="mailto:yuanhuiwu@gmail.com">Huiwu Yuan</a>
 */
@Repository(PjDao.BEAN_NAME)
public class PjDao extends Dao {
    /**
     * Bean名称
     */
    public static final String BEAN_NAME = "pjDao";

    /**
     * @param pj 项目
     * @return 项目
     */
    public Pj get(String pj) {
        String sql = "select pj,path,url,des,type from pj where pj = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return readPj(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
        return null;
    }

    /**
     * @return 项目列表
     */
    public List<Pj> getList() {
        String sql = "select pj,path,url,des,type from pj where pj not like 'demo%' order by pj";
        List<Pj> list = getDemoList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(readPj(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * @param
     * @return 获得模板项目
     */
    public List<Pj> getDemoList() {
        String sql = "select pj,path,url,des,type from pj where pj like 'demo%' ";
        List<Pj> list = new ArrayList<Pj>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(readPj(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * @param usr
     *            用户
     * @return 用户有管理权限的项目列表
     */
    public List<Pj> getAdminList(String usr) {

        String sql="" +
                "SELECT DISTINCT" +
                "	a.pj," +
                "	a.path," +
                "	a.url," +
                "	a.des," +
                "	a.type ," +
                "	b.gr  manager" +
                " FROM" +
                "	pj a ," +
                "	pj_gr_usr b" +
                " WHERE a.pj=b.pj and b.gr='manager' and b.usr=?";
        List<Pj> list = new ArrayList<Pj>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, usr);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Pj pj = readPj(rs);
                String manager = rs.getString("manager");// 是否是管理员组的用户
                pj.setManager(StringUtils.isNotBlank(manager));
                list.add(pj);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }


    /**
     * @param usr 用户
     * @return 用户有权限的项目列表
     */
    public List<Pj> getList(String usr) {
        String sql = "select p.pj,p.path,p.url,p.des,p.type,pm.pj manager from ( "
                + " select distinct a.pj,a.path,a.url,a.des,a.type from pj a where  "
                + " exists (select b.usr from pj_gr_usr b where a.pj=b.pj and b.usr=?)  "
                + " or "
                + " exists(select c.usr from pj_usr_auth c where a.pj=c.pj and c.usr=?) "
                + " ) p "
                + " left join ( "
                + " select distinct a.pj from pj a where  "
                // TODO like ? 应该是 = ? ,用like主要是兼容3.0版本 see: Issue 4
                + " exists (select b.usr from pj_gr_usr b where a.pj=b.pj and b.usr=? and b.gr like ?)"
                + " ) pm on p.pj=pm.pj";

        List<Pj> list = new ArrayList<Pj>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, usr);
            pstmt.setString(index++, usr);
            pstmt.setString(index++, usr);
            // TODO 主要是兼容3.0版本
            pstmt.setString(index++, "%" + Constants.GROUP_MANAGER);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                Pj pj = readPj(rs);
                String manager = rs.getString("manager");// 是否是管理员组的用户
                pj.setManager(StringUtils.isNotBlank(manager));
                list.add(pj);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * @param rs ResultSet
     * @return 项目
     * @throws SQLException jdbc异常
     */
    public Pj readPj(ResultSet rs) throws SQLException {
        Pj result = new Pj();
        result.setPj(rs.getString("pj"));
        result.setPath(rs.getString("path"));
        result.setUrl(rs.getString("url"));
        result.setDes(rs.getString("des"));
        result.setType(rs.getString("type"));
        return result;
    }

    /**
     * 删除
     *
     * @param pj 项目
     */
    public void delete(String pj) {
        String sql = "delete from pj where pj = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 增加项目
     *
     * @param pj 项目
     * @return 影响数量
     */
    public int insert(Pj pj) {
        String sql = "insert into pj (pj,path,url,des,type) values (?,?,?,?,?)";

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj.getPj());
            pstmt.setString(index++, pj.getPath());
            pstmt.setString(index++, pj.getUrl());
            pstmt.setString(index++, pj.getDes());
            pstmt.setString(index++, pj.getType());

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 保存项目
     *
     * @param pj 项目
     * @return 影响数量
     */
    public int update(Pj pj) {
        String sql = "update pj set path=?,url=?,des=?,type=? where pj = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj.getPath());
            pstmt.setString(index++, pj.getUrl());
            pstmt.setString(index++, pj.getDes());
            pstmt.setString(index++, pj.getType());
            pstmt.setString(index++, pj.getPj());

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 获取具有相同路径或访问地址的项目数量
     *
     * @param path 路径
     * @param url  访问地址
     * @return 具有相同路径或访问地址的项目数量
     */
    public int getCount(String path, String url) {
        String sql = "select count(1) from pj where path=? or url=?";//TODO 大小写敏感?
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, path);
            pstmt.setString(index++, url);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
        return 0;
    }
}